package org.example.多对一.dao;

import org.example.多对一.bean.DeptInfo;
import org.example.多对一.bean.EmpInfo;
import org.example.面向对象.db.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EmpInfoDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    public EmpInfo findEmpById1(int empNo){
        EmpInfo ei = new EmpInfo();
        String sql = "select * from emp,dept where emp.deptno=dept.deptno and empstate=1 and empno=?";
        conn = DBManager.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,empNo);
            rs = ps.executeQuery();
            while (rs.next()){
                ei.setEmpNo(rs.getInt(1));
                ei.setEname(rs.getString(2));
                ei.setJob(rs.getString(3));
                ei.setMgr(rs.getInt(4));
                ei.setHireDate(rs.getString(5));
                ei.setSal(rs.getDouble(6));
                ei.setComm(rs.getDouble(7));
                ei.setDeptno(rs.getInt(8));
                ei.setEmpState(1);
                DeptInfo di = new DeptInfo();
                di.setDeptNo(rs.getInt(10));
                di.setDname(rs.getString(11));
                di.setLoc(rs.getString(12));
                ei.setDi(di);
            }
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
        return ei;
    }

    public EmpInfo findEmpById2(int empNo){
        EmpInfo ei = new EmpInfo();
        String sql = "select * from emp where empno=? and empstate=1";
        conn = DBManager.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,empNo);
            rs = ps.executeQuery();
            while (rs.next()){
                ei.setEmpNo(rs.getInt(1));
                ei.setEname(rs.getString(2));
                ei.setJob(rs.getString(3));
                ei.setMgr(rs.getInt(4));
                ei.setHireDate(rs.getString(5));
                ei.setSal(rs.getDouble(6));
                ei.setComm(rs.getDouble(7));
                ei.setDeptno(rs.getInt(8));
                ei.setEmpState(1);
            }
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
        return ei;
    }
}
